package com.java.dao.impl;

import com.java.bean.Courier;
import com.java.bean.User;
import com.java.dao.BaseCourierDao;
import com.java.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseCourierDaoImpl implements BaseCourierDao {
    public static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) courier_size," +
            "COUNT(TO_DAYS(REGTIME)=TO_DAYS(NOW()) OR NULL) courier_day " +
            "FROM COURIER";
    public static final String SQL_FIND_ALL = "SELECT * FROM COURIER";
    public static final String SQL_FIND_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
    public static final String SQL_FIND_BY_ID = "SELECT * FROM COURIER WHERE ID=?";
    public static final String SQL_FIND_BY_NAME = "SELECT * FROM COURIER WHERE COURIERNAME=?";
    public static final String SQL_FIND_BY_IDNUMBER = "SELECT * FROM COURIER WHERE COURIERIDNUMBER=?";
    public static final String SQL_FIND_BY_PHONE = "SELECT * FROM COURIER WHERE COURIERPHONE=?";
    public static final String SQL_INSERT = "INSERT INTO COURIER(COURIERNAME,COURIERPHONE,COURIERIDNUMBER,COURIERPASSWORD,COURIERSENDNUMBER,REGTIME) VALUES(?,?,?,?,0,NOW())";
    public static final String SQL_UPDATE = "UPDATE COURIER SET COURIERNAME=?,COURIERPHONE=?,COURIERIDNUMBER=?,COURIERPASSWORD=? WHERE ID=?";
    public static final String SQL_UPDATE_SENDNUMBER = "UPDATE COURIER SET COURIERSENDNUMBER=COURIERSENDNUMBER+? WHERE ID=?";
    public static final String SQL_UPDATE_PRETIME = "UPDATE COURIER SET PRETIME=NOW() WHERE ID=?";
    public static final String SQL_DELETE = "DELETE FROM COURIER WHERE ID=?";


    /**
     * 查询数据库中所有的快递员数
     *
     * @return
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                int courier_size = resultSet.getInt("courier_size");
                int courier_day = resultSet.getInt("courier_day");
                Map<String,Integer> data1 = new HashMap<>();
                data1.put("courier_size",courier_size);
                data1.put("courier_day",courier_day);
                data.add(data1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return data;
    }

    /**
     * 查询所有的快递列表
     *
     * @param limit      是否分页查询
     * @param offset     起始值
     * @param pageNumber 查询页数
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            if(limit){
                statement = connection.prepareStatement(SQL_FIND_LIMIT);
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else {
                statement = connection.prepareStatement(SQL_FIND_ALL);
            }
            resultSet = statement.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String courierName = resultSet.getString("courierName");
                String courierPhone = resultSet.getString("courierPhone");
                String courierIdNumber = resultSet.getString("courierIdNumber");
                String courierPassword = resultSet.getString("courierPassword");
                int courierSendNumber = resultSet.getInt("courierSendNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                Courier c = new Courier(id,courierName,courierPhone,courierIdNumber,courierPassword,courierSendNumber,regTime,preTime);
                data.add(c);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return data;

    }

    /**
     * 根据id查询快递
     *
     * @param id
     * @return
     */
    @Override
    public Courier findById(int id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_ID);
            statement.setInt(1,id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                String courierName = resultSet.getString("courierName");
                String courierPhone = resultSet.getString("courierPhone");
                String courierIdNumber = resultSet.getString("courierIdNumber");
                String courierPassword = resultSet.getString("courierPassword");
                int courierSendNumber = resultSet.getInt("courierSendNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                Courier c = new Courier(id,courierName,courierPhone,courierIdNumber,courierPassword,courierSendNumber,regTime,preTime);
                return c;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 根据快递员姓名名查询
     *
     * @param courierName
     * @return
     */
    @Override
    public List<Courier> findByName(String courierName) {
        ArrayList<Courier> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_NAME);
            statement.setString(1,courierName);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String courierPhone = resultSet.getString("courierPhone");
                String courierIdNumber = resultSet.getString("courierIdNumber");
                String courierPassword = resultSet.getString("courierPassword");
                int courierSendNumber = resultSet.getInt("courierSendNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                Courier c = new Courier(id,courierName,courierPhone,courierIdNumber,courierPassword,courierSendNumber,regTime,preTime);
                data.add(c);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return data;
    }

    /**
     * 根据身份证查询
     *
     * @param courierIdNumber
     * @return
     */
    @Override
    public Courier findByIdNumber(String courierIdNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_IDNUMBER);
            statement.setString(1,courierIdNumber);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String courierName = resultSet.getString("courierName");
                String courierPhone = resultSet.getString("courierPhone");
                String courierPassword = resultSet.getString("courierPassword");
                int courierSendNumber = resultSet.getInt("courierSendNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                Courier c = new Courier(id,courierName,courierPhone,courierIdNumber,courierPassword,courierSendNumber,regTime,preTime);
                return c;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 根据快递员手机号查询
     *
     * @param courierPhone
     * @return
     */
    @Override
    public Courier findByPhone(String courierPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_PHONE);
            statement.setString(1,courierPhone);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String courierName = resultSet.getString("courierName");
                String courierIdNumber = resultSet.getString("courierIdNumber");
                String courierPassword = resultSet.getString("courierPassword");
                int courierSendNumber = resultSet.getInt("courierSendNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                Courier c = new Courier(id,courierName,courierPhone,courierIdNumber,courierPassword,courierSendNumber,regTime,preTime);
                return c;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 快递员的录入
     *
     * @param c
     * @return
     */
    @Override
    public boolean insert(Courier c) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.    填充参数
            state.setString(1,c.getCourierName());
            state.setString(2,c.getCourierPhone());
            state.setString(3,c.getCourierIdNumber());
            state.setString(4,c.getCourierPassword());
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 信息修改
     *
     * @param id
     * @param newCourier
     * @return
     */
    @Override
    public boolean update(int id, Courier newCourier) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //3.    填充参数
            state.setString(1,newCourier.getCourierName());
            state.setString(2,newCourier.getCourierPhone());
            state.setString(3,newCourier.getCourierIdNumber());
            state.setString(4,newCourier.getCourierPassword());
            state.setInt(5,id);
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 派件数修改
     *
     * @param id
     * @param increment
     * @return
     */
    @Override
    public boolean updateSendNumber(int id, int increment) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_SENDNUMBER);
            //3.    填充参数
            state.setInt(1,id);
            state.setInt(2,increment);
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 更新登录时间
     *
     * @param id
     * @return
     */
    @Override
    public boolean updateLoginTime(int id) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_PRETIME);
            //3.    填充参数
            state.setInt(1,id);
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id删除信息
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            //3.    填充参数
            state.setInt(1,id);
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
